﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Reflection;
using System.Windows.Forms;
using Assist;

namespace HYFamilyLibDataManager
{
	public class SqlServerFamilyTable : IFamilyTable
	{
		public SqlServerFamilyTable(FamilyLibDatabase db)
		{
			this.mDatabase = db;
		}

		public bool Add(DbCategory category, DbFamily family, DbTransaction tr)
		{
			bool result = false;
			byte[] value = null;
			if (family.Image != null)
			{
				MemoryStream memoryStream = new MemoryStream();
				family.Image.Save(memoryStream, ImageFormat.Jpeg);
				value = memoryStream.ToArray();
			}
			try
			{
				using (SqlCommand sqlCommand = ((SqlConnection)this.mDatabase.Connection).CreateCommand())
				{
					sqlCommand.Transaction = (SqlTransaction)tr;
					int num;
					if (family.Limit)
					{
						num = 1;
					}
					else
					{
						num = 0;
					}
					sqlCommand.CommandText = string.Format("insert into [FamilyTable]([FamilyName],[FamilyImg],[ParentId],[Limit],[Version],[Description],[GUID],[Date],[KeyName],[eID1],[eID2],[tag1],[tag2]) values ('{0}',{1},'{2}','{3}','{4}','{5}','{6}','{7}','{8}',{9},{10},'{11}','{12}')", new object[]
					{
						family.Name,
						"@img",
						category.Guid.ToString(),
						num,
						family.Version,
						family.Description,
						family.Guid.ToString(),
						family.Date.ToString(),
						category.KeyName,
						family.ExData.Eid1,
						family.ExData.Eid2,
						family.ExData.Tag1,
						family.ExData.Tag2
					});
					SqlParameter sqlParameter = new SqlParameter("@img", DbType.Binary);
					sqlParameter.Value = value;
					sqlCommand.Parameters.Add(sqlParameter);
					sqlCommand.ExecuteNonQuery();
					family.KeyName = category.KeyName;
					family.SetDatabase(this.mDatabase);
				}
				result = true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
			return result;
		}

		public int Count(Parent parent, CheckType checkType)
		{
			string sqlStr;
			if (checkType == CheckType.All)
			{
				sqlStr = string.Format("select [GUID] from [FamilyTable] where [ParentId]='{0}'", parent.Guid.ToString());
			}
			else if (checkType == CheckType.Checked)
			{
				sqlStr = string.Format("select [GUID] from [FamilyTable] where [ParentId]='{0}' and [Check]='{1}'", parent.Guid.ToString(), 1);
			}
			else
			{
				sqlStr = string.Format("select [GUID] from [FamilyTable] where [ParentId]='{0}' and [Check]!='{1}'", parent.Guid.ToString(), 1);
			}
			return this.mDatabase.ExecQuery(sqlStr).Rows.Count;
		}

		public int Count(CheckType checkType)
		{
			string sqlStr;
			if (checkType == CheckType.All)
			{
				sqlStr = string.Format("select [GUID] from [FamilyTable]", Array.Empty<object>());
			}
			else if (checkType == CheckType.Checked)
			{
				sqlStr = string.Format("select [GUID] from [FamilyTable] where [Check]='{0}'", 1);
			}
			else
			{
				sqlStr = string.Format("select [GUID] from [FamilyTable] where [Check]!='{0}'", 1);
			}
			return this.mDatabase.ExecQuery(sqlStr).Rows.Count;
		}

		public bool Update(DbFamily family)
		{
			int num = 0;
			if (family.Limit)
			{
				num = 1;
			}
			int num2 = 0;
			if (family.Check)
			{
				num2 = 1;
			}
			bool result;
			using (SqlCommand sqlCommand = ((SqlConnection)this.mDatabase.Connection).CreateCommand())
			{
				sqlCommand.CommandText = string.Format("update [FamilyTable] set [FamilyName]='{0}',[ParentId]='{1}',[Limit]='{2}',[Version]='{3}',[Description]='{4}',[FamilyImg]={5},[KeyName]='{6}',[eID1]={7},[eID2]={8},[tag1]='{9}',[tag2]='{10}',[Date]='{11}',[Check]='{12}',[Count]='{13}' where [GUID]='{14}'", new object[]
				{
					family.Name,
					family.ParentId.ToString(),
					num,
					family.Version,
					family.Description,
					"@img",
					family.KeyName,
					family.ExData.Eid1,
					family.ExData.Eid2,
					family.ExData.Tag1,
					family.ExData.Tag2,
					family.Date.ToString("yyyy-MM-dd HH:mm:ss"),
					num2,
					family.Count,
					family.Guid.ToString()
				});
				SqlParameter sqlParameter = new SqlParameter("@img", DbType.Binary);
				byte[] value = null;
				if (family.Image != null)
				{
					MemoryStream memoryStream = new MemoryStream();
					family.Image.Save(memoryStream, ImageFormat.Jpeg);
					value = memoryStream.ToArray();
				}
				sqlParameter.Value = value;
				sqlCommand.Parameters.Add(sqlParameter);
				if (sqlCommand.ExecuteNonQuery() != -1)
				{
					result = true;
				}
				else
				{
					result = false;
				}
			}
			return result;
		}

		public void ReDateByNow(DbFamily family)
		{
			string arg = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
			using (SqlCommand sqlCommand = ((SqlConnection)this.mDatabase.Connection).CreateCommand())
			{
				sqlCommand.CommandText = string.Format("update [FamilyTable] set [Date]='{0}' where [GUID]='{1}'", arg, family.Guid.ToString());
				sqlCommand.ExecuteNonQuery();
			}
		}

		public DbFamily this[Guid id]
		{
			get
			{
				DataTable dataTable = this.mDatabase.ExecQuery("select [FamilyImg],[Limit],[FamilyName],[ParentId],[Version],[Date],[Description],[GUID],[KeyName],[eID1],[eID2],[tag1],[tag2],[Check],[Count] from [FamilyTable] where [GUID]='" + id.ToString() + "'");
				if (dataTable != null && dataTable.Rows.Count > 0)
				{
					return this.DatatableToDbFamilyList(dataTable)[0];
				}
				return null;
			}
		}

		public DbFamily this[string name]
		{
			get
			{
				DataTable dataTable = this.mDatabase.ExecQuery("select [FamilyImg],[Limit],[FamilyName],[ParentId],[Version],[Date],[Description],[GUID],[KeyName],[eID1],[eID2],[tag1],[tag2],[Check],[Count] from [FamilyTable] where [FamilyName]='" + name + "'");
				if (dataTable != null && dataTable.Rows.Count > 0)
				{
					return this.DatatableToDbFamilyList(dataTable)[0];
				}
				return null;
			}
		}

		public List<DbFamily> this[Parent parent, CheckType checkType]
		{
			get
			{
				string sqlStr;
				if (checkType == CheckType.All)
				{
					sqlStr = string.Format("select [FamilyImg],[Limit],[FamilyName],[ParentId],[Version],[Date],[Description],[GUID],[KeyName],[eID1],[eID2],[tag1],[tag2],[Check],[Count] from [FamilyTable] where [ParentId]='{0}' ", parent.Guid.ToString());
				}
				else if (checkType == CheckType.Checked)
				{
					sqlStr = string.Format("select [FamilyImg],[Limit],[FamilyName],[ParentId],[Version],[Date],[Description],[GUID],[KeyName],[eID1],[eID2],[tag1],[tag2],[Check],[Count] from [FamilyTable] where [ParentId]='{0}' and [Check]='{1}'", parent.Guid.ToString(), 1);
				}
				else
				{
					sqlStr = string.Format("select [FamilyImg],[Limit],[FamilyName],[ParentId],[Version],[Date],[Description],[GUID],[KeyName],[eID1],[eID2],[tag1],[tag2],[Check],[Count] from [FamilyTable] where [ParentId]='{0}' and [Check]=!'{1}'", parent.Guid.ToString(), 1);
				}
				DataTable dt = this.mDatabase.ExecQuery(sqlStr);
				return this.DatatableToDbFamilyList(dt);
			}
		}

		private List<DbFamily> DatatableToDbFamilyList(DataTable dt)
		{
			List<DbFamily> list = new List<DbFamily>();
			if (dt != null && dt.Rows.Count > 0)
			{
				for (int i = 0; i < dt.Rows.Count; i++)
				{
					Image img = new Bitmap(new MemoryStream((byte[])dt.Rows[i]["FamilyImg"]));
					DbFamily dbFamily = new DbFamily(dt.Rows[i]["FamilyName"].ToString().Trim(), img, new Guid(dt.Rows[i]["GUID"].ToString()));
					dbFamily.Limit = Convert.ToBoolean(dt.Rows[i]["Limit"].ToString());
					dbFamily.Version = dt.Rows[i]["Version"].ToString().Trim();
					dbFamily.SetDate(Convert.ToDateTime(dt.Rows[i]["Date"].ToString()));
					dbFamily.Description = dt.Rows[i]["Description"].ToString().Trim();
					dbFamily.SetParentId(new Guid(dt.Rows[i]["ParentId"].ToString()));
					dbFamily.SetDatabase(this.mDatabase);
					dbFamily.KeyName = dt.Rows[i]["KeyName"].ToString();
					if (dt.Rows[i]["eID1"].ToString() != "")
					{
						dbFamily.ExData.Eid1 = Convert.ToInt32(dt.Rows[i]["eID1"].ToString());
					}
					if (dt.Rows[i]["eID2"].ToString() != "")
					{
						dbFamily.ExData.Eid2 = Convert.ToInt32(dt.Rows[i]["eID2"].ToString());
					}
					dbFamily.ExData.Tag1 = dt.Rows[i]["tag1"].ToString();
					dbFamily.ExData.Tag2 = dt.Rows[i]["tag2"].ToString();
					dbFamily.Check = (bool)dt.Rows[i]["Check"];
					dbFamily.Count = Convert.ToInt32(dt.Rows[i]["Count"].ToString());
					list.Add(dbFamily);
				}
			}
			return list;
		}

		public List<DbFamily> GetAll()
		{
			DataTable dt = this.mDatabase.ExecQuery("select [FamilyImg],[Limit],[FamilyName],[ParentId],[Version],[Date],[Description],[GUID],[KeyName],[eID1],[eID2],[tag1],[tag2],[Check],[Count] from [FamilyTable]");
			return this.DatatableToDbFamilyList(dt);
		}

		public bool Remove(DbFamily family)
		{
			DbTransaction dbTransaction = this.mDatabase.StartTransaction();
			bool result;
			try
			{
				this.mDatabase.ExecuteCommand("delete from [FamilyTable] where [GUID]='" + family.Guid.ToString() + "'", dbTransaction);
				this.mDatabase.FamilyFileTable.Remove(family, dbTransaction);
				this.mDatabase.FamilyImgTable.Remove(family, dbTransaction);
				this.mDatabase.FamilyParamTable.Remove(family, dbTransaction);
				this.mDatabase.TypeTable.Drop(family, dbTransaction);
				dbTransaction.Commit();
				result = true;
			}
			catch (Exception ex)
			{
				dbTransaction.Rollback();
				MessageBox.Show(ex.Message);
				result = false;
			}
			return result;
		}

		public FileInfo GetFamilyFile(DbFamily family)
		{
			string text = Assembly.GetExecutingAssembly().Location;
			int num = text.LastIndexOf("\\");
			text = text.Substring(0, num + 1);
			FileInfo result = null;
			DataTable dataTable = this.mDatabase.ExecQuery("select [FamilyName],[FamilyFile] from [FamilyTable] where [GUID]='" + family.Guid.ToString() + "'");
			if (dataTable != null)
			{
				byte[] pReadByte = (byte[])dataTable.Rows[0][1];
				string fileName = text + "\\HY_" + dataTable.Rows[0][0].ToString() + ".rfa";
                Assist.Assist.FileUtil.WriteFile(pReadByte, fileName);
				result = new FileInfo(fileName);
			}
			return result;
		}

		public List<DbFamily> SearchFamily(List<string> keys)
		{
			string text = "'%" + keys[0];
			for (int i = 1; i < keys.Count; i++)
			{
				text = text + "%" + keys[i] + "%";
			}
			text += "'";
			List<DbFamily> result = new List<DbFamily>();
			try
			{
				string sqlStr = "select [FamilyImg],[Limit],[FamilyName],[ParentId],[Version],[Date],[Description],[GUID],[KeyName],[eID1],[eID2],[tag1],[tag2],[Check],[Count] from [FamilyTable] where [tag1] like " + text;
				DataTable dt = this.mDatabase.ExecQuery(sqlStr);
				return this.DatatableToDbFamilyList(dt);
			}
			catch
			{
			}
			return result;
		}

		public List<DbFamily> this[Parent parent, int start, int end, CheckType checkType]
		{
			get
			{
				string sqlStr;
				if (checkType == CheckType.All)
				{
					sqlStr = string.Format("Select * from(select [FamilyImg],[Limit],[FamilyName],[ParentId],[Version],[Date],[Description],[GUID],[KeyName],[eID1],[eID2],[tag1],[tag2],[Check],[Count],ROW_NUMBER() OVER(Order by a.Date DESC ) AS RowNumber from [FamilyTable] AS a where [ParentId]='{0}') as b where RowNumber BETWEEN {1} and {2} ", parent.Guid.ToString(), start, end);
				}
				else if (checkType == CheckType.Checked)
				{
					sqlStr = string.Format("Select * from(select [FamilyImg],[Limit],[FamilyName],[ParentId],[Version],[Date],[Description],[GUID],[KeyName],[eID1],[eID2],[tag1],[tag2],[Check],[Count],ROW_NUMBER() OVER(Order by a.Date DESC ) AS RowNumber from [FamilyTable] AS a where [ParentId]='{0}' and [Check]='{1}')  as b where RowNumber BETWEEN {2} and {3} ", new object[]
					{
						parent.Guid.ToString(),
						1,
						start,
						end
					});
				}
				else
				{
					sqlStr = string.Format("Select * from(select [FamilyImg],[Limit],[FamilyName],[ParentId],[Version],[Date],[Description],[GUID],[KeyName],[eID1],[eID2],[tag1],[tag2],[Check],[Count],ROW_NUMBER() OVER(Order by a.Date DESC ) AS RowNumber from [FamilyTable] AS a where [ParentId]='{0}' and [Check]!='{1}')  as b where RowNumber BETWEEN {2} and {3} ", new object[]
					{
						parent.Guid.ToString(),
						1,
						start,
						end
					});
				}
				DataTable dt = this.mDatabase.ExecQuery(sqlStr);
				return this.DatatableToDbFamilyList(dt);
			}
		}

		public List<DbFamily> SearchFamily(string familyName, int start, int end)
		{
			List<DbFamily> result = new List<DbFamily>();
			try
			{
				DataTable dt = this.mDatabase.ExecQuery("select [FamilyImg],[Limit],[FamilyName],[ParentId],[Version],[Date],[Description],[GUID],[KeyName],[eID1],[eID2],[tag1],[tag2],[Check],[Count] from [FamilyTable] where [FamilyName] like'%" + familyName + "%'");
				return this.DatatableToDbFamilyList(dt);
			}
			catch
			{
			}
			return result;
		}

		public List<DbFamily> SearchFamily(string familyName)
		{
			List<DbFamily> result = new List<DbFamily>();
			try
			{
				DataTable dt = this.mDatabase.ExecQuery("select [FamilyImg],[Limit],[FamilyName],[ParentId],[Version],[Date],[Description],[GUID],[KeyName],[eID1],[eID2],[tag1],[tag2],[Check],[Count] from [FamilyTable] where [FamilyName] like'%" + familyName + "%'");
				result = this.DatatableToDbFamilyList(dt);
			}
			catch
			{
			}
			return result;
		}

		public int SearchFamilyCount(string familyName)
		{
			new List<DbFamily>();
			int result = 0;
			try
			{
				DataTable dataTable = this.mDatabase.ExecQuery("select * from [FamilyTable] where [FamilyName] like'%" + familyName + "%'");
				if (dataTable != null)
				{
					result = dataTable.Rows.Count;
				}
			}
			catch
			{
			}
			return result;
		}

		public List<DbFamily> SearchFamily(DateTime startTime, DateTime endTime, int start, int end)
		{
			List<DbFamily> result = new List<DbFamily>();
			try
			{
				DataTable dt = this.mDatabase.ExecQuery(string.Format("select [FamilyImg],[Limit],[FamilyName],[ParentId],[Version],[Date],[Description],[GUID],[KeyName],[eID1],[eID2],[tag1],[tag2],[Check],[Count] from [FamilyTable] where [Date] BETWEEN '{0}' AND '{1}'", startTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd")));
				result = this.DatatableToDbFamilyList(dt);
			}
			catch
			{
			}
			return result;
		}

		public List<DbFamily> SearchFamily(DateTime startTime, DateTime endTime)
		{
			List<DbFamily> result = new List<DbFamily>();
			try
			{
				DataTable dt = this.mDatabase.ExecQuery(string.Format("select [FamilyImg],[Limit],[FamilyName],[ParentId],[Version],[Date],[Description],[GUID],[KeyName],[eID1],[eID2],[tag1],[tag2],[Check],[Count] from [FamilyTable] where [Date] BETWEEN '{0}' AND '{1}'", startTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd")));
				result = this.DatatableToDbFamilyList(dt);
			}
			catch
			{
			}
			return result;
		}

		public int SearchFamilyCount(DateTime startTime, DateTime endTime)
		{
			new List<DbFamily>();
			int result = 0;
			try
			{
				DataTable dataTable = this.mDatabase.ExecQuery(string.Format("select * where [Date] BETWEEN '{0}' AND '{1}'", startTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd")));
				if (dataTable != null)
				{
					result = dataTable.Rows.Count;
				}
			}
			catch
			{
			}
			return result;
		}

		private FamilyLibDatabase mDatabase;
	}
}
